package br.ufrj.neg.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;

import br.ufrj.neg.usuario.bean.UsuarioBean;

public class UsuarioUsuarioModel {
	
	
	public Collection<UsuarioBean> listaOutros(int id){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT * FROM USUARIO WHERE USUA_CD_ID IN (" +
					"	SELECT usua_cd_id_1 as first FROM USUARIO_USUARIO WHERE USUA_CD_ID_2=?" +
					"	UNION" +
					"	SELECT usua_cd_id_2 as first FROM USUARIO_USUARIO WHERE USUA_CD_ID_1=?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setInt(2, id);
			rs = ps.executeQuery();
			Collection<UsuarioBean> c = new ArrayList<UsuarioBean>();
			while (rs.next()){
				UsuarioBean ub = new UsuarioBean();
				ub.setDescricao(rs.getString("USUA_DS_USUARIO"));
				ub.setId(rs.getInt("USUA_CD_ID"));
				ub.setLogin(rs.getString("USUA_DS_LOGIN"));
				ub.setPapel(rs.getInt("PAPE_CD_ID"));
				ub.setSenha(rs.getString("USUA_CD_SENHA"));
				c.add(ub);
			}
			return c;
		}catch(Exception e){
			System.out.println(e);
			return null;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
		
	}
}
